﻿#region UsingStatements

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

#endregion

namespace RuleCode.ObjectTree
{
    public class SqlHelper
    {
        private static SqlHelper _Instance;

        //private const string _SqlConnectionString = 
        private static string _SqlConnectionString = string.Empty;
        private static string SqlConnectionString
        {
            get
            {
                //#if DEBUG
                //                return "Data Source=.;Initial Catalog=test;User ID=test;Password=test";
                //                //return Path.GetDirectoryName(Application.ExecutablePath) + "\\ObjData";
                //#endif
               // return "Data Source=192.168.0.2;Initial Catalog=ObjectTree;User ID=dev";

                if (String.IsNullOrEmpty(_SqlConnectionString))
                {
                    try
                    {
                        _SqlConnectionString = Util.GetConnectString();
                    }
                    catch
                    {
                    }
                }
                if (string.IsNullOrEmpty(_SqlConnectionString))
                    _SqlConnectionString = "Data Source=.;Initial Catalog=NewDev;Integrated Security=true";
                return _SqlConnectionString;
            }
            set
            {
                _SqlConnectionString = value;
            }
        }

        private static SqlConnection _SqlConnection;
        
        public static void UpdateSqlConnection()
        {
            SqlConnectionString = Util.GetConnectString();
            _SqlConnection = new SqlConnection(SqlConnectionString);
        }

        public SqlHelper()
        {
            _SqlConnection = new SqlConnection(SqlConnectionString);
        }

        /// <summary>
        /// 对SqlHelper自身的调用 静态方法
        /// </summary>
        public static SqlHelper Instance
        {
            get
            {
                if (_Instance == null)
                    _Instance = new SqlHelper();
                return _Instance;
            }
        }

        /// <summary>
        /// 利用动态的创建SQL命令 获取 数据库读取的对象 SqlDataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string cmdText)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _SqlConnection;
            cmd.CommandText = cmdText;
            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                return rdr;
            }
            catch
            {
                _SqlConnection.Close();
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="myPara"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] myParas)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _SqlConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (myParas != null)
            {
                foreach (SqlParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }

            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                _SqlConnection.Close();
                return null;
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="myPara"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] myParas)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _SqlConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (myParas != null)
            {
                foreach (SqlParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }


            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                object val = cmd.ExecuteScalar();

                cmd.Parameters.Clear();
                return val;
            }
            catch
            {
                _SqlConnection.Close();
                return null;
            }
        }

        /// <summary>
        /// ExecuteNonQuery
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="myParas"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] myParas)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _SqlConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (myParas != null)
            {
                foreach (SqlParameter pama in myParas)
                    if (pama != null)
                        cmd.Parameters.Add(pama);
                    else
                        break;
            }

            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                _SqlConnection.Close();
            }
            return -1;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _SqlConnection;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            SqlTransaction trans = null;

            //修改说明：加了BegingTransaction和Commit看能否解决添加字段不稳定的问题。

            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                trans = _SqlConnection.BeginTransaction();
                cmd.Connection = _SqlConnection;
                cmd.Transaction = trans;
                int val = cmd.ExecuteNonQuery();
                trans.Commit();

                return val;
            }
            catch (Exception e0)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                MessageBox.Show(e0.ToString());
                //不再次抛出
                //throw;
            }
            finally
            {
                _SqlConnection.Close();
            }
            return -1;
        }

        #region 数据库属性相关
        /// <summary>
        /// 创建新的数据库，并且是否使用新的数据库
        /// </summary>
        /// <param name="dataBaseName">数据库名称</param>
        /// <param name="overWrite">是否覆盖已有同名的数据库</param> 
        /// <param name="useNewDB">是否创建后使用该数据库连接</param>
        /// <returns></returns>
        public int CreatDataBase(string dataBaseName,bool overWrite,bool useNewDB,string cnnStr)
        {
            string overWriteCmdText = "if exists (select name from master.dbo.sysdatabases where name='"+dataBaseName +"') Drop DataBase ["+dataBaseName +"] ";
            string notExistCmdText = "if not exists (select name from master.dbo.sysdatabases where name='"+dataBaseName +"') ";
            string createCmdText = "Create DataBase ["+dataBaseName+"]" ;
            string cmdText =overWrite ? (overWriteCmdText +createCmdText ):(notExistCmdText +createCmdText ) ;

            SqlConnection cnn = null;
            
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (cnnStr != string.Empty)
                    cnn = new SqlConnection(cnnStr);
                else
                    cnn = _SqlConnection;
                if (cnn.State != ConnectionState.Open)
                    cnn.Open();
                cmd.Connection = cnn;
                cmd.CommandText = cmdText;

                int val = cmd.ExecuteNonQuery();
                if (useNewDB)
                {
                    SqlConnectionStringBuilder cnnConnectStrBuilder = new SqlConnectionStringBuilder(_SqlConnection.ConnectionString);
                    cnnConnectStrBuilder.InitialCatalog = dataBaseName;
                    _SqlConnection = new SqlConnection(cnnConnectStrBuilder.ConnectionString);
                    val = 1;
                }
                return val;
            }
            catch
            {
            }
            finally {
                if(cnn!=null)
                    cnn.Close();
                if(_SqlConnection !=null && _SqlConnection.State == ConnectionState.Open  )
                    _SqlConnection.Close();
            }
            return -1;
        }

        /// <summary>
        /// 判断是否存在 cmdText中的结果
        /// </summary>
        /// <param name="cmdText">数据库执行语句</param>
        /// <returns></returns>
        public bool ExistCmdText(string cmdText)
        {
            bool result = false ;
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (_SqlConnection.State != ConnectionState.Open)
                    _SqlConnection.Open();
                cmd.Connection = _SqlConnection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cmdText;
                SqlDataReader rdr = cmd.ExecuteReader();
                result = rdr != null && rdr.HasRows;
            }
            finally {
                _SqlConnection.Close();
            }
            return result;
        }

        /// <summary>
        /// 判断数据库中是否存在表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool ExistTable(string tableName)
        {                         
            string cmdText = "select id from dbo.sysobjects where id=object_id(N'"+tableName +"') and (OBJECTPROPERTY(id,N'IsUserTable')=1)";
            return ExistCmdText(cmdText);
        }

        /// <summary>
        /// 判断是否存在存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public bool ExistProc(string procName)
        {
            string cmdText = "select   name   from   sysobjects   where   name='"+procName +"'   and   xtype='p'";
            return ExistCmdText(cmdText);
        }

        /// <summary>
        /// 判断是否存在数据库
        /// </summary>
        /// <param name="dataBaseName"></param>
        /// <returns></returns>
        public bool ExistDataBase(string dataBaseName)
        {
            string cmdText = "SELECT   name   FROM   master.dbo.sysdatabases   WHERE   name   =   N'"+dataBaseName +"'";
            return ExistCmdText(cmdText);
        }

        /// <summary>
        /// 判断是否在表中存在某个字段
        /// </summary>
        /// <param name="columnName">字段名称</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public bool ExistColumn(string columnName,string tableName)
        {
            string cmdText = "select  * from syscolumns where id=object_id('"+tableName +"') and name ='"+columnName +"'";
            return ExistCmdText(cmdText);
        }

        /// <summary>
        /// 判断表中的索引是否存在
        /// </summary>
        /// <param name="indexName">索引名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool ExistIndex(string indexName, string tableName)
        {
            string cmdText = "select * from sysindexes  where name ='"+indexName +"' and id= Object_ID('"+tableName +"')";
            return ExistCmdText(cmdText);
        }
        #endregion

        #region 静态方法
        /// <summary>
        /// 执行SQL脚本
        /// </summary>
        /// <param name="sqlConnectStr">链接字符串</param>
        /// <param name="sqlScript">SQL脚本</param>
        public static void ExecSqlScript(string sqlConnectStr, string sqlScript)
        {
            SqlConnection cnn =null;
            try
            {
                cnn = new SqlConnection(sqlConnectStr);
                if (cnn != null)
                {
                    if (cnn.State != ConnectionState.Open)
                        cnn.Open();

                    SqlCommand cmd = new SqlCommand(sqlScript );
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception error)
            {
            }
            finally
            {
                if(cnn!=null)
                    cnn.Close();
            }
        }
        #endregion
    }
}